﻿IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_SCBCJL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_SCBCJL]
GO

/**********

[版本号]1.0.0
[创建时间]2018-12-15
[作者]Z_DD
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--首次病程记录GXBZ_EMR_SCBCJL数据上传
[返回值]
[调用的USP]
	[调用实例] EXEC USP_GXBZ_EMR_SCBCJL
[修改历史]
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_SCBCJL]    
AS 
  SET XACT_ABORT ON 
DECLARE 
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN
   

	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_SCBCJL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_SCBCJL','2015-07-01')
	END
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_SCBCJL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM EMR_MJZBL(NOLOCK)

	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN
	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_SCBCJL(
		ZYJZLSH		varchar(64)          not null,
		XGBZ		varchar(1)           not null,
		GRJBXXBSH	varchar(64)          not null,
		CXJMJKDABH	varchar(17)          not null,
		CJSJ		datetime             null,
		WDCJZGH		varchar(64)          null,
		WDCJZXM		varchar(30)          null,
		WDGLJGDM	varchar(22)          null,
		WDGLJGMC	varchar(22)          null,
		JLRQSJ		datetime             null,
		SJYSGH		varchar(64)          null,
		SJYSXM		varchar(30)          null,
		ZYYSGH		varchar(64)          null,
		ZYYSXM		varchar(30)          null,
		BCH			varchar(10)          null,
		BCHMC		varchar(50)          null,
		BFH			varchar(10)          null,
		BFHMC		varchar(50)          null,
		KSBM		varchar(20)          null,
		KSMC		varchar(100)         null,
		BQBM		varchar(20)          null,
		BQMC		varchar(100)         null,
		YLJGDM		varchar(22)          null,
		YLJGMC		varchar(70)          null,
		ZS			varchar(1024)        null,
		BLTDMS		varchar(2000)        null,
		ZYSZGCJG	varchar(1024)        null,
		ZDYJ		varchar(100)         null,
		XYCZDM		varchar(64)          null,
		XYCZMC		varchar(512)         null,
		ZYCZDM		varchar(64)          null,
		ZYCZMC		varchar(512)         null,
		ZYCZZHDM	varchar(64)          null,
		ZYCZZHMC	varchar(512)         null,
		XYJZMC		varchar(512)         null,
		ZYJZMC		varchar(512)         null,
		ZYJZZHMC	varchar(512)         null,
		ZLJH		varchar(2000)        null,
		ZZZF		varchar(100)         null,
		MJ			varchar(16)          not null,
		TBRQ		datetime             not null,
		SCZT		varchar(1)           not null,
		CS_JYBZ		int                  not null,
		SCSJ		datetime             not null
   )ON [PRIMARY]
   
	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_SCBCJL (
		ZYJZLSH,
		XGBZ,
		GRJBXXBSH,
		CXJMJKDABH,
		CJSJ,
		WDCJZGH,
		WDCJZXM,
		WDGLJGDM,
		WDGLJGMC,
		JLRQSJ,
		SJYSGH,
		SJYSXM,
		ZYYSGH,
		ZYYSXM,
		BCH,
		BCHMC,
		BFH,
		BFHMC,
		KSBM,
		KSMC,
		BQBM,
		BQMC,
		YLJGDM,
		YLJGMC,
		ZS,
		BLTDMS,
		ZYSZGCJG,
		ZDYJ,
		XYCZDM,
		XYCZMC,
		ZYCZDM,
		ZYCZMC,
		ZYCZZHDM,
		ZYCZZHMC,
		XYJZMC,
		ZYJZMC,
		ZYJZZHMC,
		ZLJH,
		ZZZF,
		MJ,
		TBRQ,
		SCZT,
		CS_JYBZ,
		SCSJ)
	SELECT
		A.ZYJZLSH ZYJZLSH,
		A.XGBZ XGBZ,
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) GRJBXXBSH,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) AS CXJMJKDABH,
		A.JLRQSJ CJSJ,
		'' WDCJZGH,
		'' WDCJZXM,
		'500103000000001' WDGLJGDM,
		'渝中区卫计委' WDGLJGMC,
		A.JLRQSJ JLRQSJ,
		A.SJYSGH SJYSGH,
		A.SJYSQM SJYSXM,
		A.ZYYSGH ZYYSGH,
		A.ZYYSQM ZYYSXM,
		A.BCH BCH,
		A.BCH+'病床' BCHMC,
		A.BFH BFH,
		A.BFH+'病房' BFHMC,
		A.KSBM KSBM,
		A.KSMC KSMC,
		'' BQBM,
		A.BQMC BQMC,
		isnull(C.SPTDM,'500103000000001')  YLJGDM,
		isnull(C.JGMC,'渝中区卫计委') YLJGMC,
		A.ZS ZS,
		A.BLTD BLTDMS,
		A.ZYSZGCJG ZYSZGCJG,
		A.ZDYJ ZDYJ,
		A.CBXYZDDM XYCZDM,
		A.CBXYZDMC XYCZMC,
		A.CBZYBMDM ZYCZDM,
		A.CBZYBMMC ZYCZMC,
		A.CBZYZHDM ZYCZZHDM,
		A.CBZYZHMC ZYCZZHMC,
		A.JBXYZDMC XYJZMC,
		A.JBZYBMMC ZYJZMC,
		A.JBZYZHMC ZYJZZHMC,
		A.ZLJH ZLJH,
		A.ZZZF ZZZF,
		A.MJ MJ,
		A.TBRQ TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ		
	FROM EMR_SCBCJL A(NOLOCK)
	LEFT JOIN ZYJZJLB B(NOLOCK) ON A.ZYJZLSH=B.JZLSH
	LEFT JOIN SYS_ZCJG_DZ C(NOLOCK) ON A.YLJGDM=C.QPTDM
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01') 
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = B.ZJHM) is not NULL
	
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_SCBCJL A,#TEMP_GXBZ_EMR_SCBCJL B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.ZYJZLSH=B.ZYJZLSH
	
	--插入数据至中间库
	INSERT INTO GXBZ_EMR_SCBCJL(
		ZYJZLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,JLRQSJ,
		SJYSGH,SJYSXM,ZYYSGH,ZYYSXM,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,YLJGDM,YLJGMC,
		ZS,BLTDMS,ZYSZGCJG,ZDYJ,XYCZDM,XYCZMC,ZYCZDM,ZYCZMC,ZYCZZHDM,ZYCZZHMC,XYJZMC,ZYJZMC,
		ZYJZZHMC,ZLJH,ZZZF,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ)
	SELECT
		ZYJZLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,JLRQSJ,
		SJYSGH,SJYSXM,ZYYSGH,ZYYSXM,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,YLJGDM,YLJGMC,
		ZS,BLTDMS,ZYSZGCJG,ZDYJ,XYCZDM,XYCZMC,ZYCZDM,ZYCZMC,ZYCZZHDM,ZYCZZHMC,XYJZMC,ZYJZMC,
		ZYJZZHMC,ZLJH,ZZZF,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ
	FROM #TEMP_GXBZ_EMR_SCBCJL
	
	COMMIT TRAN
	
		SELECT 'T' AS SRESULT, '表：GXBZ_EMR_SCBCJL数据抽取成功！' AS SHINT
		
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
		VALUES('表：GXBZ_EMR_SCBCJL数据抽取成功','GXBZ_EMR_SCBCJL',GETDATE(),'T','USP_WD_GXBZ_EMR_SCBCJL')
		
			--更新上传时间
			UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_SCBCJL'
			
	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_SCBCJL数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN
		
		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_SCBCJL数据抽取失败','GXBZ_EMR_SCBCJL',GETDATE(),'F','USP_GXBZ_EMR_SCBCJL',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END